from pysqlite2 import dbapi2 as sqlite
from math import sqrt

db = sqlite.connect('database_test_10')

# clear out the current Similarity tables
db.execute('drop table if exists cosSim')
db.execute('create table cosSim(linkid primary key, value)')

# initialize every url with a PageRank of 1
db.execute('insert into cosSim select rowid, 0.0 from link')
count = 0;
for (linkid, fromid, toid,) in db.execute("select rowid, fromid, toid from link where toid in (select distinct urlid from linkword)").fetchall():
	count +=1;
	print count;
	sim = 0.0
	sq1 = 0
	sq2 = 0

	sim = db.execute('select sum(A.freq*B.freq) from linkword as A, linkword as B where A.urlid = %d and B.urlid = %d and A.wordid = B.wordid' %(fromid,toid)).fetchone()[0];
	sq1 = db.execute('select sum(A.freq * A.freq) from linkword as A where A.urlid = %d' %(fromid)).fetchone()[0];
	sq2 = db.execute('select sum(A.freq * A.freq) from linkword as A where A.urlid = %d' %(toid)).fetchone()[0];

	if sq2 == 0:
		continue

	root = sqrt(sq1*sq2)
	sim = sim/root
#	print str(sq1) + ' ' + str(sq2) + ' ' + str(sim) + ' ' + str(root)

	db.execute('update cosSim set value=%f where linkid=%d' % (sim,linkid))

db.commit()
